
if exists (select 1 from sysobjects where name = 'get_planshema' and type = 'P')
begin
   drop procedure get_planshema
   print 'Procedure: get_planshema deleted ...'
end
go
create procedure get_planshema(
  @schemaid         int               = 25
)
as
begin
  set nocount on

  select schemaname = s.Name,
         schemaid = s.SchemaID,
         prsschemaid = ps.PrsSchemaID,
         prsid = ps.PrsID,
         vorname = isnull(p.Vorname,''),
         nachname = isnull(p.Nachname,''),
         abteilungid = ps.AbteilungID,
         abteilungname = a.Name,
         rgb = ps.RGB,
         color = ps.ColorName
    from PlanSchema ps
   inner join Schemas s
      on s.SchemaID = ps.SchemaID
    left outer join Person p
      on p.PrsId = ps.PrsID
   inner join Abteilung a
      on a.AbteilungID = ps.AbteilungID
   where ps.SchemaID = @schemaid
   order by ps.PrsSchemaID

end
go
print 'Procedure: get_planshema done ...'
go
grant exec on get_planshema to prsadmins with grant option
go
grant exec on get_planshema to prsusers
go

